A White Paper on SQLWindows and Lotus Notes Integration
July 1995
Gupta Corporation and Lotus Development announced a close technology partnership in 1993. Lotus Corporation is the leader in delivering tools to integrate the workgroup environment. Gupta Corporation is the leader in providing high productivity tools for data access and creating PC client-server applications. Together, Lotus Development Corporation and Gupta Corporation will be able to provide the premier solutions to today's information management problems.
Since its introduction in 1989, Lotus Notes has emerged as the standard for integrated groupware. Groupware applications lets groups share information in different ways to fit their own specific needs. Lotus Notes is the leading database for groupware applications because of its unique, distributed document architecture. Gupta Corporation has integrated SQLWindows with the Lotus Notes environment. Gupta Corporation accomplished this by tightly integrating the services offered by the Notes environment into its products and by allowing applications built using these products to be easily included in the Notes environment. This paper will describe this integration and its benefits.
By integrating SQLWindows with Notes, Gupta Corporation offers database programmers with a unique and powerful "Notes-Ready"product. Database programmers know that Notes provides the premier method of organizing, viewing and storing unstructured data, including documents, files and smart objects. SQLWindows provides the premier means of building robust and flexible applications that typically access structured data in relational database systems. Now, with SQLWindows, programmers can build quick and powerful applications that integrate structured data in relational database systems, such as Oracle, Microsoft and DB2, with Notes databases.
A Notes database is a collection of unstructured data, typically stored in documents. A document is a collection of fields which can include standard text fields, radio buttons, check boxes, pushbuttons, rich text fields, document links (which are used to include other documents from a Notes database) and attachments (which can be any type of file).
A Notes database also typically includes Notes applications. Notes applications are built in the Notes environment using tools supplied with Lotus Notes. The Notes applications are the vehicles through which Notes users interact with Notes databases. Notes applications can be used to insert, retrieve and organize the data in a Notes database as well as trigger actions based on the contents of the document or the actions of the user.
Notes also offers replication services. Replication services can automate the copying and synchronization of documents from one Notes database to another. This replication can be conditional. Replication services are often used to distribute Notes documents over widely distributed Notes locations.
These Lotus Notes features help users to manage information in the following ways:
Collecting - Notes accepts information from a variety of sources, and can store text, tables, and graphics as compound documents
Organizing - Notes makes it easy to organize large amounts of document-oriented information
Sharing - Notes allows users to immediately share information with others
Processing - Notes makes it easy to process information by passing documents from one person to the next, with each adding to or refining information in the document
There are two constituencies building applications with or evaluating Lotus Notes. The first group consists of Lotus Notes programmers, consultants, and end-users who have little knowledge of SQL databases, but have corporate data in these databases that they will access via Notes. The second group, corporate organizations, have been SQL-centric for many years, and are now expanding their environment to incorporate Notes. The unstructured document architecture of Lotus Notes is complementary to traditional database systems. Notes applications differ from client/server RDBMS applications in these ways:
Programmers in both constituencies want an application development environment for Notes to have these features:
Migrate data to and from SQL databases Access data in SQL databases from within the Notes desktop Build applications rapidly, with easier layout and coding Write reports against Notes and SQL data
Table 1: Notes-SQL programmers want these features
SQLWindows provides many Notes-enabled features that enable Notes programmers to access SQL, and SQL programmers to access Notes. The first offering from Gupta for Notes developers was the Lotus Notes Class Library that shipped with SQLWindows 5.0 in July 1994. Gupta will soon be introducing QuickObjects for Lotus Notes to provide enhanced ease-to-use integration between SQLWindows and Notes.
The new QuickObjects for Lotus Notes in SQLWindows provide programmers with an intuitive, point-and-click approach to building applications for Lotus Notes. In less than 90 seconds, SQLWindows programmers can interface to Lotus Notes. Programmers can also integrate with Lotus Notes Mail and cc:Mail services just as quickly.
The QuickObjects include smart objects that all Notes programmers and users depend on. The first is a Notes View QuickObject that can display Notes documents in a hierarchical view, just like in Lotus Notes. This QuickObject allows a user to expand and collapse the view, launch the document by double-clicking, and compose response documents. SQLWindows is also the only application development tool that provides a Notes Rich Text Field QuickObject which can be directly placed in an application accessing both SQL databases and Notes. This QuickObject displays text in multiple fonts and colors, tables, file attachments and OLE objects, and allows the user to edit the contents within a SQLWindows application. With these controls, SQLWindows is more like Lotus Notes than any other development tool.
Finally, SQLWindows enables programmers to integrate data from Lotus Notes and SQL databases in a few seconds by pointing and clicking. No programming was required to build this application that coordinates data from a SQL database and a Notes database in a "master-detail" relationship
More sophisticated applications can also be built with these QuickObjects. SQLWindows will include a comprehensive set of sample programs that aid first time programmers. With these QuickObjects for Lotus Notes, programmers have a unique and powerful "Notes-Ready"product to integrate Notes and SQL databases.
IBM's pending acquisition of Lotus Development Corporation will not adversely affect Gupta's close integration with Lotus Notes. In fact, both IBM and Gupta Corporation have worked together closely for many years. This is evident in Gupta's superior connectivity to IBM's mainframe-based databases via Gupta's SQLHost for DB2 and SQLHost/Application Services for CICS access to IMS, VSAM and other legacy data. When Lotus merges with IBM, developers can expect continued support and even tighter integration between SQLWindows and Lotus Notes, IBM's AS/400, DB2 and other legacy databases.
Programmers want to tie together different pieces of data from both structured and semi-structured sources, specifically the ability to integrate relational SQL and Notes data. SQLWindows provides an easy-to-use mechanism for building applications that interact with Notes information. With QuickObjects for Lotus Notes, both SQL and Notes programmers will be able to create custom applications that access these varied sources of data in an easy yet integrated manner.
A three-tier application architecture encourages the separation of the graphical interface (tier 1) from the business logic (tier 2) and the database layer (tier 3). While this separation into three tiers is a key requirement, there should preferably be additional flexibility to locate each tier on any popular hardware, and sometimes each tier on a separate machine.
Using a three-tier architecture can potentially deliver greater application scalability, improved performance, greater network security and easier administration than can be achieved with a two-tier application model. The extent of benefit obtained can vary significantly depending on the application.
While two tier applications can be constructed quickly and maintained easily, especially if component-based development is undertaken, three-tier applications usually require a lot more planning, effort and skill for development and maintenance, especially since this architecture is new to the industry and is yet to mature.
It all depends on the complexity of your project, degree of skill your staff posess, delivery deadline, etc. While many applications are best implemented with two tiers, very large applications can benefit from a third tier. The key is to weigh the costs of a three-tier setting against forecasted benefits. For example, due diligence should be exercised to ensure that excessive network traffic or an extra software layer does not reduce application performance or lower overall network throughput.
Three-tier architecture alone can not deliver application scalability. Other critical pre-requisites for scalability are robust object orientation, repository-based team support, project management tools, high performance through compilation of business rules to native machine executables, and direct, optimized links to databases. SQLWindows includes these critical pre-requisites today, and will add significant improvements later this year. Application scalability can also be improved by a three-tier data architecture, discussed later.
There is no single "best" way to implement a three-tier application architecture. The choice greatly depends on your application requirements, operating environment, timeframe for delivery, skills and resources. For maximum flexibility, Gupta is supporting a variety of methods which you can choose from.
Stored procedures separate business rules from the GUI, provide code reuse among clients by centralizing code on the server, and improve network performance. They do not impose additional skill or time requirements for network access. Therefore, they are the easiest way to get started with three-tier architecture and allow you to partition your application's business logic between client and server machines. The limitations are that they force tier 2 and 3 to be on the same machine and are not database independent. The next SQLWindows version makes multi-database stored procedure access considerably easier.
Some vendors like Oracle aggressively promote app-lication partitioning via stored procedures as a primary three-tier option. While SQLWindows can fully support everything that vendors like Oracle promise to, since the same programming language is used in both SQL- Windows and SQLBase stored procedures, Gupta bel-ieves that database-independent three-tier is desired by many customers, and will deliver it as described below.
Another technique that SQLWindows programmers have employed to implement three-tier is to use transaction processing (TP) monitors. A TP monitor is useful when the application will be deployed to hundreds or thousands of concurrent clients, thereby imposing stringent requirements for reliability and performance. In this case, the business rules are written in a 3GL like C or COBOL and made available as a TP monitor "service". SQLWindows can directly link to IBM's CICS TP monitor through an add-on Gupta product, SQLHost/ Application Services. SQLWindows customers have also successfully integrated with Novell's Tuxedo by writing external functions. Gupta is now working on providing an easy to use, pre-built graphical link to Tuxedo, and subsequently extending that to other TP monitors too.
Today, it is possible to write remote functions that contain business logic (tier 2) in a 3GL like C and invoke those functions over the network from the client by calling SQLWindows "external functions". Some cust-omers have taken this approach to implementing three-tier with SQLWindows today. While this approach works, many customers prefer to write the business rules in SQLWindows rather than C and want to be insulated from the network-level details. To facilitate this, Gupta will support remote business objects, described below.
This feature is an object-oriented adaptation of what is traditionally called "application partitioning". In this scenario, you can easily encapsulate business rules into "business objects" during development and just before deployment decide where on the network to locate the business object. The business object is tier 2 and contains business rules and supporting data managament code to access data on tier 3. Communication between the client and remote business object needs to be supported by a middleware layer, preferably based on an industry standard protocol like TCP/IP or DCE. In its U.S. Developers Conference in June, Gupta demonstrated the new business objects capability. This will be made available in the next major SQLWindows release in 1995. Engineering work to locate these business objects remotely on the network is also in full swing and will quickly follow the release of the business objects. In addition to locating these business objects remotely, Gupta will provide facilties for management of the objects in an integrated and reliable manner.
While there are many methods of providing communication between tiers 1, 2 and 3, Gupta is relying on standards compliant middleware based on the TCP/IP and DCE protocols, and will take full advantage of Microsoft's Distributed OLE and Component Object Model (COM) when they become available. Gupta is also relying on third-party interoperability gateways to bridge to other standards like CORBA and DSOM.
Most organizations with applications complex enough to benefit from a three-tier application will likely also benefit from a three-tier data architecture, where data is decentralized across the enterprise in multiple tiers.
On the top tier, corporate-wide data is stored on large systems-including mainframes, minicomputers and new parallel processing servers. Corporate-wide servers, for example, will continue to be critical for managing large on-line transaction processing systems.
Beneath this, subsets of corporate data are stored in branch offices and LAN workgroups. These servers, at the heart of most client/server projects today, are rapidly becoming an integral part of today's MIS infrastructure.
At Gupta, we are beginning to see a new tier emerge with mobile client/server technology. In this tier, data is deployed to individuals with laptops and notebooks who need to access data outside the traditional bounds of the corporate office.
The benefit of this three-tier data architecture is greater capacity for decentralized concurrent data access, reduced system contention, greater network throughput, better response time, and more end-user flexibility.
Gupta fully supports and encourages use of a three-tier data architecture with SQLNetwork and SQLBase. Gupta's SQLNetwork provides connectivity to a wide range or popular database systems at any tier in your organization. Gupta's SQLBase is a database management system optimized for easy, cost-efficient data deployment on decentralized platforms ranging from mobile systems to powerful workgroup servers.
Using the above products, many Gupta customers commonly build SQLWindows applications today that integrate data from multiple tiers. For example, insurance agents may run a policy and claims application against a workgroup database server, such as SQLBase or SQLServer, when in the branch office. When they are in the field with customers, they run this application on their laptops against a local database, such as SQLBase. Each night, data from the branch office is exchanged with a central DB2 database, located in corporate headquarters, using SQLNetwork.
In coming months, Gupta will further facilitate three-tier data
architectures by providing pre-built data replication. These replication
facilities will operate heterogenously across database brands
to synchronize data in the multiple tiers. With this, customers
will be able to easily implement a three-tier application and
data architecture.